package com.ms.qw.infraestructura.persistence.jpa.sigar;

import com.ms.support.jpa.BaseDAO;
import com.ms.qw.client.dto.CheckPointDTO;
import com.ms.qw.domain.model.CheckPoint;
import com.ms.qw.domain.model.CheckPointRepository;
import com.ms.qw.server.infrastructure.persistence.util.WhereParams;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.stereotype.Repository;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * User: aglwkrs
 * Date: 30/11/11
 */
@Repository
public class CheckPointRepositoryImpl extends BaseDAO<CheckPoint, Long> implements CheckPointRepository {

    private final static Logger logger = LoggerFactory.getLogger(CheckPointRepositoryImpl.class);

    private JdbcTemplate jdbcTemplate;
    private NamedParameterJdbcTemplate namedParameterjdbcTemplate;


    @Autowired
    DataSource dataSource;

    @PostConstruct
    public void init() {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.namedParameterjdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    }

    public Map findConfigYear(Long checkPointId) {
        return (Map) jdbcTemplate.queryForMap("SELECT [id], [ano1], [ano2], [ano3], [ano4] " +
                "    FROM [TM_CONF_REPORTES_CXP] where [checkpoint_id] = ? ",new Object[]{checkPointId});
    }

    public void procesarPeriodos(Long checkPointId) {
       int maxYear= Integer.parseInt(jdbcTemplate.queryForObject("select max(year(fechaContable)) from dbo.SGTM_OPERACION_CUENTA where checkpoint_id = ?",
               new Object[]{checkPointId}, String.class));

        jdbcTemplate.update("UPDATE [dbo].[dbo.SGTM_OPERACION_CUENTA](SET [ano1]=? , [ano2] = ?, [ano3] = ? , [ano4] = ?) " +
                "    WHERE CHECKPOINT_ID = ? ",new Object[]{maxYear-3,maxYear-2,maxYear-1,maxYear,checkPointId});
    }

    public List<CheckPointDTO> findLast() {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT TOP 5  CHECKPOINT_ID        as  \"id\"   , ");
        sql.append("       NO_ARCHIVO    as  \"nombreArchivo\"    , ");
        sql.append("       accion as  \"codigoAccion\", ");
        sql.append("       FECHA_CREACION  as  \"fechaCreacion\", ");
        sql.append("       comentario as  \"comentario\" ");
        sql.append("FROM   dbo.SGTM_CHECKPOINT   order by  FECHA_CREACION desc ");
        WhereParams params = new WhereParams();
        return namedParameterjdbcTemplate.query(sql.toString(), params.getParams(), new BeanPropertyRowMapper<CheckPointDTO>(CheckPointDTO.class));

    }



    private class UsuarioSolicitanteCxP extends StoredProcedure {
        public UsuarioSolicitanteCxP(JdbcTemplate template) {
            super(template, "sp_rep_usuarioSolicitante_CXP");
            setFunction(false);
            declareParameter(new SqlParameter("idCheckPoint", Types.INTEGER));
            declareParameter(new SqlParameter("sociedad", Types.INTEGER));
            compile();
        }

        public void execute(int idCheckPoint, int sociedad) {
            HashMap input = new HashMap();
            input.put("idCheckPoint", idCheckPoint);
            input.put("sociedad", sociedad);
            execute(input);
        }

    }

    private class UsuarioSolicitanteGrupCxP extends StoredProcedure {
        public UsuarioSolicitanteGrupCxP(JdbcTemplate template) {
            super(template, "sp_rep_agrup_usuarioSolicitante_CXP");
            setFunction(false);
            declareParameter(new SqlParameter("idCheckPoint", Types.INTEGER));
            declareParameter(new SqlParameter("agrupacion_id", Types.INTEGER));
            declareParameter(new SqlParameter("sociedad", Types.INTEGER));
            compile();
        }

        public void execute(int idCheckPoint, int agrupacion_id, int sociedad) {
            HashMap input = new HashMap();
            input.put("idCheckPoint", idCheckPoint);
            input.put("agrupacion_id", agrupacion_id);
            input.put("sociedad", sociedad);
            execute(input);
        }

    }

    private class ProveedoresCxP extends StoredProcedure {
        public ProveedoresCxP(JdbcTemplate template) {
            super(template, "sp_rep_proveedores_CXP");
            setFunction(false);
            declareParameter(new SqlParameter("idCheckPoint", Types.INTEGER));
            declareParameter(new SqlParameter("sociedad", Types.INTEGER));
            compile();
        }

        public void execute(int idCheckPoint,int sociedad) {
            HashMap input = new HashMap();
            input.put("idCheckPoint", idCheckPoint);
            input.put("sociedad", sociedad);
            execute(input);
        }

    }



    private class AgrupacionSignosCxP extends StoredProcedure {
        public AgrupacionSignosCxP(JdbcTemplate template) {
            super(template, "sp_rep_agrupacion_por_signos");
            setFunction(false);
            declareParameter(new SqlParameter("idCheckPoint", Types.INTEGER));
            declareParameter(new SqlParameter("sociedad", Types.INTEGER));
            compile();
        }

        public void execute(Long idCheckPoint, Long sociedad) {
            HashMap input = new HashMap();
            input.put("idCheckPoint", idCheckPoint.intValue());
            input.put("sociedad", sociedad.intValue());
            execute(input);
        }

    }
    private class AgrupacionAnosCxP extends StoredProcedure {
        public AgrupacionAnosCxP (JdbcTemplate template) {
            super(template, "sp_rep_agrupacion_anos_CXP");
            setFunction(false);
            declareParameter(new SqlParameter("idCheckPoint", Types.INTEGER));
            declareParameter(new SqlParameter("agrupacionid", Types.INTEGER));
            declareParameter(new SqlParameter("sociedad", Types.INTEGER));
            compile();
        }

        public void execute(Long idCheckPoint,Long agrupacionId, Long sociedad) {
            HashMap input = new HashMap();
            input.put("idCheckPoint", idCheckPoint.intValue());
            input.put("agrupacionid", agrupacionId.intValue());
            input.put("sociedad", sociedad.intValue());
            execute(input);
        }

    }
}
